package management.system.model.dao;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import management.system.model.bean.IdeaDTO;
import management.system.model.interfaces.IdeaDAO;
import management.system.utils.MySqlDBConn;

public class MySqlIdeaDAO implements IdeaDAO{


	public ArrayList<IdeaDTO> listIdea() throws SQLException {
		// TODO Auto-generated method stub
		ArrayList<IdeaDTO> lstIdea = new ArrayList<IdeaDTO>();
		IdeaDTO dto = null;
		String sql = null;
		sql = "Select * From Idea;";
		
		System.out.println("sQL:"+sql);
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			PreparedStatement pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			FileInputStream fis = null;
			
			while(rs.next()){
				dto = new IdeaDTO();
				dto.setIdeaId(rs.getInt("Idea_ID"));
				dto.setPerfilId(rs.getInt("Perfil_ID"));
				dto.setTitulo(rs.getString("Titulo"));
				dto.setDescripcion(rs.getString("Descripcion"));
				dto.setImagen(rs.getString("Imagen"));
				dto.setEstado(rs.getString("Estado"));
				dto.setPalabraClave1(rs.getString("Palabra_clave1"));
				dto.setPalabraClave2(rs.getString("Palabra_clave2"));
				dto.setPalabraClave3(rs.getString("Palabra_clave3"));
				dto.setIdeaId(rs.getInt("Usuario_ID"));
				lstIdea.add(dto);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return lstIdea;
	}

	public Integer insertIdea(IdeaDTO dto) throws SQLException {
		// TODO Auto-generated method stub
		String sql = null;
		Integer result = null;
		sql = "Insert into idea (Perfil_ID, Titulo, Descripcion, Imagen, Estado, Palabra_clave1, "+
			 " Palabra_clave2, Palabra_clave3, Palabra_clave4, Usuario_ID, Fecha_registro, Fecha_modificacion) " +
			 " values(?, ?, ?, ?, 'E', ?, ?, ?, ?, ?, now(), now());";
		
		System.out.println("sQL:"+sql);
		Connection conn = null;
		
		try {
			conn = getConnection();
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, dto.getPerfilId());
			pstm.setString(2, dto.getTitulo());
			pstm.setString(3, dto.getDescripcion());
			pstm.setString(4, dto.getImagen());
			pstm.setString(5, dto.getPalabraClave1());
			pstm.setString(6, dto.getPalabraClave2());
			pstm.setString(7, dto.getPalabraClave3());
			pstm.setString(8, dto.getPalabraClave4());
			pstm.setInt(9, dto.getUsuarioId());
			
			result = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	public Integer updateIdea(IdeaDTO dto) throws SQLException {
		// TODO Auto-generated method stub
		String sql = null;
		Integer result = null;
		sql = "Update idea set  Titulo=?, Descripcion=?, Imagen=?, Estado=?, Palabra_clave1=?, " +
			  " Palabra_clave2=?, Palabra_clave3=?, Palabra_clave4=?, Usuario_ID=?, Fecha_modificacion=? " +
			  " Where Idea_ID=? and Perfil_ID=?;";
		
		System.out.println("sQL:"+sql);
		Connection conn = null;
		
		try {
			conn = getConnection();
			PreparedStatement pstm = conn.prepareStatement(sql);

			pstm.setString(1, dto.getTitulo());
			pstm.setString(2, dto.getDescripcion());
			pstm.setString(3, dto.getImagen());
			pstm.setString(4, dto.getPalabraClave1());
			pstm.setString(5, dto.getPalabraClave2());
			pstm.setString(6, dto.getPalabraClave3());
			pstm.setString(7, dto.getPalabraClave4());
			pstm.setInt(8, dto.getUsuarioId());
			pstm.setTimestamp(9, dto.getFechaModificacion());
			pstm.setInt(10, dto.getIdeaId());
			pstm.setInt(11, dto.getPerfilId());
			
			result = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	public Integer deleteIdea(IdeaDTO dto) throws SQLException {
		// TODO Auto-generated method stub
		String sql = null;
		Integer result = null;
		
		sql = "";
		
		System.out.println("sQL:"+sql);
		Connection conn = null;
		
		try {
			conn = getConnection();
			PreparedStatement pstm = conn.prepareStatement(sql);

			
			result = pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return result;
	}

	public IdeaDTO getIdeaById(Integer id) throws SQLException {
		// TODO Auto-generated method stub
		IdeaDTO dto = null;
		String sql = null;
		sql = "Select * From Idea Where Idea_ID=?;";
		
		System.out.println("sQL:"+sql);
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, id);
			
			rs = pstm.executeQuery();
			
			while(rs.next()){
				dto = new IdeaDTO();
				dto.setIdeaId(rs.getInt("Idea_ID"));
				dto.setPerfilId(rs.getInt("Perfil_ID"));
				dto.setTitulo(rs.getString("Titulo"));
				dto.setDescripcion(rs.getString("Descripcion"));
				dto.setImagen(rs.getString("Imagen"));
				dto.setEstado(rs.getString("Estado"));
				dto.setPalabraClave1(rs.getString("Palabra_clave1"));
				dto.setPalabraClave2(rs.getString("Palabra_clave2"));
				dto.setPalabraClave3(rs.getString("Palabra_clave3"));
				dto.setIdeaId(rs.getInt("Usuario_ID"));
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return dto;
	}
	
	public Integer getIdeaID() throws SQLException {
		String sql = null;
		Integer result = null;
		ResultSet rs = null;
		
		sql = "Select max(Idea_ID)+1 From Idea;";
		
		System.out.println("sQL:"+sql);
		Connection conn = null;
		
		try {
			conn = getConnection();
			PreparedStatement pstm = conn.prepareStatement(sql);
			
			rs = pstm.executeQuery();
			
			if(rs.next())
			result = rs.getInt(0);
		} catch (SQLException e) {
			// TODO: handle exception
		}
		return result;
	}

	
	public Connection getConnection(){
		MySqlDBConn mysql = new MySqlDBConn();
		return mysql.getConnection();
	}
	
	

}
